<?php
namespace Excel;

use app\crm\model\BusinessStatus;
use think\facade\Db;
use think\facade\Queue;

/**
 * Excel 封装导入导出类
 * @package extend\Excel
 */
class Excel
{
    /**
     * 导出Excel
     * @param string $expTitle 文件名称
     * @param array $expCellName 表头
     * @param array $expTableData 数据
     * @param array $mergeCells 合并单元格
     * @author HongPing <hongping626@qq.com>
     * @alter zl
     */
    public static function export($expTitle = '', $expCellName = [], $expTableData = [], $mergeCells = [])
    {
        if ( $expTitle == '' || empty($expCellName) ) {
            $message = $expTitle == '' ? '请填写文件名!': '请填写表头!';
            return ["error" => 1, 'message' => $message];
        }

        $file_type = 'xlsx';
        if (strpos($expTitle, '.')) {
            $file_name = explode('.', $expTitle);
            if (strtolower(end($file_name)) != 'xls' && strtolower(end($file_name)) != 'xlsx') {
                $file_name = $expTitle.'.xls';
            } else {
                $file_type = end($file_name);
                $file_name = $expTitle;
            }
        } else {
            $file_name = $expTitle.'.xls';
        }

        $file_type = strtolower($file_type) == 'xls' ? 'Excel5' : 'Excel2007';

        if (ob_get_length()) ob_end_clean();

        $fileName = $file_name; //or $xlsTitle 文件名称可根据自己情况设定
        $cellNum  = count($expCellName);
        $dataNum  = count($expTableData);


        $objPHPExcel = new \PHPExcel();
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ'];
        //合并单元格
        if (!empty($mergeCells)) {
            foreach ($mergeCells as $mergeCell) {
                $objPHPExcel->getActiveSheet()->mergeCells($mergeCell);
            }
        }

        $objSheet = $objPHPExcel->getActiveSheet();

        for($i=0;$i<$cellNum;$i++){
            $objSheet->setCellValue($cellName[$i].'1', $expCellName[$i][2]);
            //根据内容设置单元格宽度
            $cellWidth = $expCellName[$i][1] == 'auto' ? strlen($expCellName[$i][2]) : $expCellName[$i][1];
            $objSheet->getColumnDimension($cellName[$i])->setWidth($cellWidth);
            // $objPHPExcel->getActiveSheet(0)->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][1]);
            $objSheet->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        }

        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                if (isset($expTableData[$i][$expCellName[$j][0]])) {
                    $objSheet->setCellValueExplicit($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]], \PHPExcel_Cell_DataType::TYPE_STRING);
                }
            }
        }
        //下载
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$fileName);
        header("Content-Transfer-Encoding:binary");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $file_type);
        $objWriter->save('php://output');
        exit();
    }

    /**
     * 导入Excel
     * @param array $file upload file $_FILES 上传文件信息
     * @param null $table 导入到指定表，如果不指定，则返回拼接的数据，不执行导入
     * @param null $fields 字段名 只导入指定字段名的数据，excel表中其他在字段名找不到的，将不导入
     *         传入格式：array('id' => 'id', 'username' => '用户名')，key为数据库字段名，value为excel表头名
     * @param int $type 导入模式，0默认为增量导入（导入并跳过已存在的数据），1为覆盖导入（导入并覆盖已存在的数据）
     * @param null $where 查询依据，做配合查询使用
     * @param null $main_field 作为判断导入模式依据的主要字段，比如指定为KSH，则用KSH这个字段来判断是否已存在数据库
     * @author HongPing <hongping626@qq.com>
     * @alter zl
     * @return array
     */
    public static function import($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null)
    {
        if(empty($file) || !file_exists($file['tmp_name'])){
            return ["error" => 1, 'message' => '文件未找到!']; //file not found!
        }

        $file_info = explode('.', $file['name']);
        $file_ext  = strtolower(end($file_info));
        if ($file_ext != 'xls' && $file_ext != 'xlsx') {
            return ["error" => 1, 'message' => '文件类型不正确!'];
        }

        $file_type = $file_ext == 'xls' ? 'Excel5' : 'Excel2007';

        $objReader = \PHPExcel_IOFactory::createReader($file_type); //需要在前面加反斜杠，因为命名空间
        try{
            $PHPReader = $objReader->load($file['tmp_name']);
        }catch(\Exception $e){}

        if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!

        $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
        $i = 0;
        $array = [];

        //循环读取每个工作表
        foreach($allWorksheets as $objWorksheet){
            $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
            $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
            $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
            $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
            $array[$i]["Title"] = $sheet_name;
            $array[$i]["Cols"]  = $allColumn;
            $array[$i]["Rows"]  = $allRow;
            $arr = [];
            $isMergeCell = [];
            foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
                foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                    $isMergeCell[$cellReference] = true;
                }
            }

            //循环读取每行数据
            for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
                $row = [];
                for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                    $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                    $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                    $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                    $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                    $address = $col.$currentRow;//当前单元格的位置
                    $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                    $value   = trim($value); //去除首尾空格
                    // 如果当前行的第一个单元格内容为空，则跳过当前行
                    if ($currentColumn == 0 && ($value == '' || $value === NULL)) {
                        break;
                    }
                    if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                        return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                    }

                    //判断单元格是否为数字类型
                    if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                        // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                        $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                        $format_code = $cell_style_format->getFormatCode();
                        if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                            $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                        }else{
                            $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                        }
                    }
                    //处理该单元格为合并单元格的情况
                    $temp = '';
                    if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                        $temp = $value;
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                        $value = $arr[$currentRow-1][$currentColumn];
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                        $value = $temp;
                    }
                    $row[$currentColumn] = $value;
                }

                $arr[$currentRow] = $row; //完整一行的数据
            }
            $array[$i]["Content"] = array_filter($arr);//过滤空行
            $i++;
        }

        unset($objWorksheet);
        unset($PHPReader);
        unset($PHPExcel);

        // 没有指定表名，则直接返回拼接结果
        if ($table === null) {
            return ["error" => 0, "data" => $array];
        }
        if ($fields === null) {
            return ["error" => 2, 'message' => '未指定字段名!'];
        }
        if ($type != 0 && $type != 1) {
            return ["error" => 3, 'message' => '导入模式不正确!'];
        }
        if ($main_field === null) {
            return ["error" => 4, 'message' => '未指定用于判断依据的字段名!'];
        }
        if (!is_string($main_field)) {
            return ["error" => 5, 'message' => '用于判断依据的字段名只能为字符串类型!'];
        }

        //查询已经存在的数据，用于判断导入模式做对比
        $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);

        //整理数据
        $fields    = array_flip($fields); //反转键值
        $data_list = [];
        $dataAdd['list']   = [];
        $dataCover['list'] = [];
        $dataSkip['list']  = [];
        foreach ($array as $key => $value) { //循环每一张工作表
            $firstRow = [];
            foreach ($value['Content'] as $row => $col) { //循环每一行数据
                $data = [];
                if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($fields[$val])) {
                            $firstRow[$index] = $fields[$val];
                        }
                    }
                } else { //这里开始是真正需要的数据
                    if (empty($firstRow)) {
                        return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                    }
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($firstRow[$index])) {
                            $data[$firstRow[$index]] = trim($val);
                        }
                    }

                    // 判断导入模式
                    if ($type == 0) {//增量导入
                        if (in_array($data[$main_field], $exists_list)) {
                            $dataSkip['list'][] = $data[$main_field]; //记录跳过的数据
                            continue;//跳过已存在的考生
                        } else {
                            $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
                        }
                    } else {//覆盖导入
                        if (in_array($data[$main_field], $exists_list)) {
                            $dataCover['list'][] = $data[$main_field]; //记录覆盖的数据
                            $map[$main_field]    = $data[$main_field];
                            Db::name($table)->where($where)->where($map)->delete();//删除已存在的数据
                        } else {
                            $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
                        }
                    }

                    $data_list[] = $data;
                }
            }
        }

        if ($data_list) {
            if (Db::name($table)->insertAll($data_list)) {
                //计算新增、覆盖、跳过的数量
                $dataAdd['total']   = count($dataAdd['list']);
                $dataCover['total'] = count($dataCover['list']);
                $dataSkip['total']  = count($dataSkip['list']);
                //将新增、覆盖、跳过的数据写入缓存
                cache('dataAdd', $dataAdd);
                cache('dataCover', $dataCover);
                cache('dataSkip', $dataSkip);
                cache('nextUrl', null);
                return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'];
            } else {
                return ["error" => 9, 'message' => '导入失败!请重新导入。'];
            }
        } else {
            return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'];
        }
    }

    /**
     * 导入Excel
     * @param $file
     * @param $create_user_id
     * @return array
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @author HongPing <hongping626@qq.com>
     * @alter zl
     */
    public static function first_import($path, $create_user_id)
    {
        set_time_limit(0);
        ini_set ('memory_limit', '-1');
        // $filePath = './storage/'.$path;
        $filePath = app()->getRootPath().'public/'.$path;


        // if($path == '' || !file_exists($filePath)){
        //     return ["error" => 1, 'message' => '文件未找到!']; //file not found!
        // }
        // $file_info = explode('.', $path);
        // $file_ext  = strtolower(end($file_info));
        // if ($file_ext != 'xls' && $file_ext != 'xlsx') {
        //     return ["error" => 1, 'message' => '文件类型不正确!'];
        // }

        // $file_type = $file_ext == 'xls' ? 'Excel5' : 'Excel2007';

        // $objReader = \PHPExcel_IOFactory::createReader($file_type); //需要在前面加反斜杠，因为命名空间
        // try{
        //     $PHPReader = $objReader->load($filePath);
        // }catch(\Exception $e){
        //     return ["error" => 1, 'message' => $e->getMessage()];
        // }

        // if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!

        // $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
        // $i = 0;
        // $array = [];

        //循环读取每个工作表
        // foreach($allWorksheets as $objWorksheet){
        //     $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
        //     $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
        //     $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
        //     $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
        //     $array[$i]["Title"] = $sheet_name;
        //     $array[$i]["Cols"]  = $allColumn;
        //     $array[$i]["Rows"]  = $allRow;
        //     $arr = [];
        //     $isMergeCell = [];
        //     foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
        //         foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
        //             $isMergeCell[$cellReference] = true;
        //         }
        //     }

        //     //循环读取每行数据
        //     for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
        //         $row = [];
        //         for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
        //             $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
        //             $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
        //             $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
        //             $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
        //             $address = $col.$currentRow;//当前单元格的位置
        //             $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
        //             $value   = trim($value); //去除首尾空格
        //             // 如果当前行的第一个单元格内容为空，则跳过当前行
        //             if ($currentColumn == 0 && ($value == '' || $value === NULL)) {
        //                 break;
        //             }
        //             //处理该单元格为合并单元格的情况
        //             $temp = '';
        //             if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
        //                 $temp = $value;
        //             }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
        //                 $value = isset($arr[$currentRow-1][$currentColumn]) ? $arr[$currentRow-1][$currentColumn]: '';
        //             }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
        //                 $value = $temp;
        //             }
        //             $row[$currentColumn] = $value;
        //         }

        //         $arr[$currentRow] = $row; //完整一行的数据
        //     }
        //     $array[$i]["Content"] = array_filter($arr);//过滤空行
        //     $i++;
        // }

        // unset($objWorksheet);
        // unset($PHPReader);
        // unset($PHPExcel);

        // $contentArray = $array[0]['Content'];
        
        $firstStr = substr($filePath, 0,1);
      if( $firstStr == '/' ){
          $filePath = '.'.$filePath; 
      }
      $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($filePath); //传入Excel路径
      $excelReader   = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType); //Xlsx
      $PHPExcel      = $excelReader->load($filePath); // 载入excel文件
      $sheet         = $PHPExcel->getSheet(0); // 读取第一個工作表
      $contentArray = $sheetData = $sheet->toArray();
      // if( $dropFirstRow == true ){
        array_shift($contentArray);    // 去掉首行 标题头
        array_shift($contentArray);
      // }
      // halt( $sheetData);

        // 数据处理
        for ($rows = 0; $rows <= count($contentArray)+1; $rows++) {
            if(!empty($contentArray[$rows])){
                // 当前任务将由哪个类来负责处理。 当轮到该任务时，系统将生成一个该类的实例，并默认调用其 fire 方法
                $jobHandlerClassName = 'app\admin\job\ImportUser';

                //当前任务归属的队列名称，如果为新队列，会自动创建
                //php think queue:work --queue importJobQueue
                //php think queue:work --queue importJobQueue --daemon
                $jobQueueName = "importJobQueue";

                //数组数据
                $orderData = [
                    'rows'           => $contentArray[$rows], // excel表格数据
                    'create_user_id' => $create_user_id,
                ];

                //将该任务推送到消息队列，等待对应的消费者去执行
                //这里只是负责将数据添加到相应的队列名称的队列里，消费者与生产者并无联系
                Queue::push($jobHandlerClassName , $orderData, $jobQueueName);
            }
        }
        // unlink($filePath);
        return ["error" => 0, 'message'=> '导入成功'];
    }



    /**
     * 导出csv
     * @param $fileName
     * @param $fieldList
     * @param $exportData
     * @author Mr.Li
     * @create 2021-06-09 14:12
     */
    public static function dataExportCsv($fileName, $fieldList, $exportData)
    {
        ini_set('memory_limit', '128M');
        set_time_limit(0);

        //调试时，先把下面这个两个header注释即可
        header("Access-Control-Expose-Headers: Content-Disposition");
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header("Content-Disposition:attachment;filename=" . $fileName . ".csv");

        header('Expires: 0');
        header('Cache-control: private');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Content-Description: File Transfer');
        header('Content-Encoding: UTF-8');
        // 加上bom头，防止用office打开时乱码
        echo "\xEF\xBB\xBF";    // UTF-8 BOM

        // 打开PHP文件句柄，php://output 表示直接输出到浏览器
        $fp = fopen('php://output', 'a');

        // 将中文标题转换编码，否则乱码
        foreach ($fieldList as $i => $v) {
            $title_cell[$i] = $v['name'];
        }
        // 将标题名称通过fputcsv写到文件句柄
        fputcsv($fp, $title_cell);

        foreach ($exportData as $item) {
            $rows = [];
            foreach ($fieldList as $rule) {
                $rows[] = $item[$rule['field']];
            }
            fputcsv($fp, $rows);
        }
        // 将已经写到csv中的数据存储变量销毁，释放内存占用
        ob_flush();
        flush();
        fclose($fp);
        exit();
    }

}
